package Mappages;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DAO.CL_dao;

public class CL_mp_poi {
	
	private CL_dao odao;
	private ResultSet rs;
	private PreparedStatement stmt;
	private CL_mp_lieu olieu;
	public CL_mp_poi() {
		this.odao = new CL_dao();
		
	}

	public int M_ajouter_poi(String libelle, double X, double Y, String description, String lien, String image)
	{
		//int id_poi = 0;
		List<Integer>id_poi = new ArrayList<Integer>(); 

			String request = "INSERT INTO `tb_poi` " +
							"( `LIBELLE`,  `COORDONNEE_Y`,  `COORDONNEE_X`, `DESCRIPTION`, `LIEN`, `IMAGE`) " +
							"VALUES ( ?, ?, ?, ?, ?, ?, ?)";
			try {
				this.stmt = this.odao.getConn().prepareStatement(request);
				this.stmt.setString(1, libelle);
				this.stmt.setDouble(2, Y);
				this.stmt.setDouble(3, X);
				this.stmt.setString(4, description);
				this.stmt.setString(5, lien);
				this.stmt.setString(6, image);
				this.stmt.executeUpdate();
			}catch (Exception e) {
				throw new RuntimeException("La requete n'est pas valide -- le prog va s'arreter");
			}
			
			try {
				
			stmt = this.odao.getConn().prepareStatement("SELECT `ID_POI` FROM tb_poi ORDER BY `ID_POI` DESC LIMIT 1;");	
						
			rs = stmt.executeQuery();
			
			while (rs.next()) 
			{
				int id = rs.getInt("id_poi");				
				id_poi.add(id);
							
			}
			stmt.close();
		} catch (Exception e) {
			// TODO: handle exception
			throw new RuntimeException("pb dans la requete last id");
		}
			return id_poi.get(0);
	}
		
		
		
		// Requete : INSERT INTO `tb_poi`(`ID_POI`, `LIBELLE`, `COORDONNEE_Y`, `COORDONNEE_X`, `DESCRIPTION`, `LIEN`, `IMAGE`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5],[value-6],[value-7])
		

	
	public void M_modifier_poi (int id_poi, String libelle, String description, String lien, String image)
	{
		
		try {
				
				stmt = this.odao.getConn().prepareStatement("UPDATE `tb_poi` SET `LIBELLE`= ? ,`DESCRIPTION`= ? ,`LIEN`= ? ,`IMAGE`= ? WHERE `ID_POI` = ? ;");	
				
					
				stmt.setString(1, libelle);
				stmt.setString(4, description);
				stmt.setString(5, lien);
				stmt.setString(6, image);	
				stmt.setInt(7, id_poi);	
				
				stmt.executeUpdate();
				stmt.close();
				
			} catch (Exception e) {
				// TODO: handle exception
				throw new RuntimeException("La requete n'est pas valide -- le prog va s'arreter");
			}
			
	}
	
	public void M_supprimer_poi (int id_poi){
		try {
			
			stmt = this.odao.getConn().prepareStatement("DELETE FROM `tb_poi` WHERE `ID_POI` = ?;");			
				
			stmt.setInt(1, id_poi);	
			
			stmt.executeUpdate();
			stmt.close();
			
		} catch (Exception e) {
			// TODO: handle exception
			throw new RuntimeException("La requete n'est pas valide -- le prog va s'arreter");
		}
		
}
	public int M_recuperer_ID_poi (String nom){
		int ID = 0 ;
		String requete = "SELECT `ID_POI` FROM tb_poi WHERE `LIBELLE` = ?";
		try {
			this.stmt = this.odao.getConn().prepareStatement(requete);
			this.stmt.setString(1, nom);

			rs = stmt.executeQuery();

			rs.next();
			ID = rs.getInt(1);
			
			stmt.close();
		}  catch (Exception e) {
			// TODO: handle exception
			throw new RuntimeException("La requete n'est pas valide -- le prog va s'arreter");
		}
		return ID;
	}
	
	
 	public ResultSet M_select_poi (){
		
		rs = null;
		try {
			String rqt = "SELECT * FROM TB_POI";
			this.stmt = this.odao.getConn().prepareStatement(rqt);
			rs = stmt.executeQuery();
			
		} catch (SQLException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		} 

		//this.odao.M_deconnection();
		return rs;	
	
	}
	

	//Assesseur
	
	public PreparedStatement getStmt() {
		return stmt;
	}

	public void setStmt(PreparedStatement stmt) {
		this.stmt = stmt;
	}

	public ResultSet getRs() {
		return rs;
	}

	public void setRs(ResultSet rs) {
		this.rs = rs;
	}


	
	public ResultSet m_select_poi(String poi, String lieu, String pays){
		String request = "";
		this.olieu = new CL_mp_lieu();
		
		
		try {
			request = "SELECT * FROM `mini_sig`.`tb_poi` WHERE 1=1 ";
			if(!poi.equals("") && !lieu.equals("")){
				int id = olieu.m_select_id_by_name(lieu, pays);
				request = "SELECT * FROM `mini_sig`.`tb_poi` WHERE `tb_poi`.`LIBELLE` LIKE ? AND `tb_poi`.`ID_LIEU` = ?";
				this.stmt = this.odao.getConn().prepareStatement(request);
				this.stmt.setString(1, "%" + poi + "%");
				this.stmt.setInt(2, id);
				this.rs = stmt.executeQuery();
			} else if(!lieu.equals("") && poi.equals("")){
				int id = olieu.m_select_id_by_name(lieu, pays);
				request = "SELECT * FROM `mini_sig`.`tb_poi` WHERE `tb_poi`.`ID_LIEU` = ?";
				this.stmt = this.odao.getConn().prepareStatement(request);
				this.stmt.setInt(1, id);
				this.rs = stmt.executeQuery();
			} else if(!poi.equals("") && lieu.equals("")) {
				request = "SELECT * FROM `mini_sig`.`tb_poi` WHERE `tb_poi`.`LIBELLE` LIKE ?";
				this.stmt = this.odao.getConn().prepareStatement(request);
				this.stmt.setString(1, "%" + poi + "%");
				this.rs = stmt.executeQuery();
			} else if(poi.equals("") && lieu.equals("")) {
				request = "SELECT * FROM `mini_sig`.`tb_poi`";
				this.stmt = this.odao.getConn().prepareStatement(request);
				this.rs = stmt.executeQuery();
			}
			//execution de la requette
			
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	
	public void M_new_poi (String libelle, double X, double Y, String description, String lien, String image, int id_lieu){
			String request = "INSERT INTO `mini_sig`.`tb_poi` " +
					"( `LIBELLE`,  `COORDONNEE_Y`,  `COORDONNEE_X`, `DESCRIPTION`, `LIEN`, `IMAGE`,`ID_LIEU` ) " +
					"VALUES ( ?, ?, ?, ?, ?, ?, ?)";
			try {
					this.stmt = this.odao.getConn().prepareStatement(request);
					this.stmt.setString(1, libelle);
					this.stmt.setDouble(2, Y);
					this.stmt.setDouble(3, X);
					this.stmt.setString(4, description);
					this.stmt.setString(5, lien);
					this.stmt.setString(6, image);	
					this.stmt.setLong(7, id_lieu);
					this.stmt.executeUpdate();
					this.stmt.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
	public void modif_poi(String nom, int id){
		String request = "UPDATE  `mini_sig`.`tb_poi` SET  `LIBELLE` =  '?' WHERE  `tb_poi`.`ID_POI` = ? ;";
		try {
				this.stmt = this.odao.getConn().prepareStatement(request);
				this.stmt.setString(1, nom);
				this.stmt.setInt(2, id);
				this.stmt.executeUpdate();
				this.stmt.close();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
	}
	



		

}
	
